package tt.dz.biz.dao.user;

import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.vo.IdentityAccountVO;
import tt.dz.vo.UserBaseVO;

@Repository("identityAccountDAO")
public class IdentityAccountDAO
  extends BaseDAO
{
  private static final String INSERT_IDENTITY_ACCOUNT = "insert into ms_identity_account(card_no,password,user_id,card_type,card_bin,area_code,check_code,state,init_date,init_oper,open_oper,open_channel,open_date,valid_date,merchant_code)  values(:cardNo,:password,:userId,:cardType,:cardBin,:areaCode,:checkCode,:state,:initDate,:initOper,:openOper,:openChannel,:openDate,:validDate,:merchantCode )";
  private static final String LIST_ALL_IDENTITY_ACCOUNT = "select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  from ms_identity_account as a ";
  private static final String QUERY_USER_BY_IDENTITY_ACCOUNT = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t ,  ms_identity_account a  where  t.user_id = a.user_id and a.card_no = :cardNo ";
  private static final String LIST_ALL_IDENTITY_ACCOUNT_CONVERT = "select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  ,CASE when a.state = 0 THEN '未激活' when a.state = 1 THEN '激活' when a.state = 2 THEN '冻结' when a.state = 3 THEN '挂失' when a.state = 4 THEN '注销' end as stateDesc ,b.name as merchantdesc, mm.name as  enterpriseName   from ms_identity_account  a , ms_merchant b ,ms_user_base c  ,ms_merchant mm  where  b.merchant_code = a.merchant_code and a.user_id=c.user_id and c.enterprise_code = mm.merchant_code ";
  private static final String UPDATE_IDENTITY_ACCOUNT = "update  ms_identity_account set password = :password,user_id = :userId,card_type = :cardType,card_bin = :cardBin,area_code = :areaCode,check_code = :checkCode,state = :state,init_date = :initDate,init_oper = :initOper,open_oper = :openOper,open_channel = :openChannel,open_date = :openDate,valid_date = :validDate,merchant_code = :merchantCode where card_no = :cardNo";
  private static final String QUERY_IDENTITY_ACCOUNT_BY_USER = "select DISTINCT a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code FROM ms_user_relationship b ,  ms_identity_account a where b.relate_user_id = a.user_id  and  b.user_id = :userId";
  private static final String LIST_ALL_IDENTITY_CARDS = "select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code ,mm.name as  enterpriseName  from\tms_identity_account A,ms_user_base ub,ms_merchant mm  where a.user_id = ub.user_id and ub.enterprise_code = mm.merchant_code  order by a.init_date desc ";
  private static final String QUERY_USERID_BY_CARDNO = "select ur.user_id from ms_identity_account ia,ms_user_relationship ur where ia.user_id = ur.relate_user_id and ur.is_valid = 1 and  ia.card_no = :cardNo";
  
  public UserBaseVO queryUserByIdentityAccount(IdentityAccountVO identityAccountVO)
  {
    String sql = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.allow_over_quota,t.login_times,t.is_online,t.password,t.is_locked,t.locked_time,t.failure_times,t.is_valid  from ms_user_base as t ,  ms_identity_account a  where  t.user_id = a.user_id and a.card_no = :cardNo ";
    if (StringUtils.isNotBlank(identityAccountVO.getPassword())) {
      sql = sql + " and a.password = :password ";
    }
    List<UserBaseVO> list = queryForList(sql, identityAccountVO, UserBaseVO.class);
    if ((list != null) && (!list.isEmpty())) {
      return (UserBaseVO)list.get(0);
    }
    return null;
  }
  
  public List<IdentityAccountVO> listAllIdentityAccount()
  {
    return queryForList("select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code ,mm.name as  enterpriseName  from\tms_identity_account A,ms_user_base ub,ms_merchant mm  where a.user_id = ub.user_id and ub.enterprise_code = mm.merchant_code  order by a.init_date desc ", null, IdentityAccountVO.class);
  }
  
  public List<IdentityAccountVO> queryIdentityAccountByUser(UserBaseVO baseVO)
  {
    return queryForList("select DISTINCT a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code FROM ms_user_relationship b ,  ms_identity_account a where b.relate_user_id = a.user_id  and  b.user_id = :userId", baseVO, IdentityAccountVO.class);
  }
  
  public List<IdentityAccountVO> queryIdentityAccount(IdentityAccountVO identityAccountVO)
  {
    String sql = "select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  from ms_identity_account as a  where 1 = 1 " + assemblyConditions(identityAccountVO, "a", false);
    return queryForList(sql, identityAccountVO, IdentityAccountVO.class);
  }
  
  public List<IdentityAccountVO> queryIdentityAccountAndConvert(IdentityAccountVO identityAccountVO)
  {
    String sql = "select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  ,CASE when a.state = 0 THEN '未激活' when a.state = 1 THEN '激活' when a.state = 2 THEN '冻结' when a.state = 3 THEN '挂失' when a.state = 4 THEN '注销' end as stateDesc ,b.name as merchantdesc, mm.name as  enterpriseName   from ms_identity_account  a , ms_merchant b ,ms_user_base c  ,ms_merchant mm  where  b.merchant_code = a.merchant_code and a.user_id=c.user_id and c.enterprise_code = mm.merchant_code    " + assemblyConditions(identityAccountVO, "a", false);
    return queryForList(sql, identityAccountVO, IdentityAccountVO.class);
  }
  
  public int insertIdentityAccount(IdentityAccountVO identityAccountVO)
  {
    return save("insert into ms_identity_account(card_no,password,user_id,card_type,card_bin,area_code,check_code,state,init_date,init_oper,open_oper,open_channel,open_date,valid_date,merchant_code)  values(:cardNo,:password,:userId,:cardType,:cardBin,:areaCode,:checkCode,:state,:initDate,:initOper,:openOper,:openChannel,:openDate,:validDate,:merchantCode )", identityAccountVO);
  }
  
  public IdentityAccountVO queryUserByIdentifyCardNo(String cardNo)
  {
    try
    {
      StringBuilder sql = new StringBuilder("select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  from ms_identity_account as a ");
      sql.append("where  a.card_no=:cardNo and a.state=1 ");
      IdentityAccountVO param = new IdentityAccountVO();
      param.setCardNo(cardNo);
      return (IdentityAccountVO)queryForObject(sql.toString(), param, IdentityAccountVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public int[] bacthUpdateIdentityAccount(List<IdentityAccountVO> identityAccountList)
  {
    return batchSave("update  ms_identity_account set password = :password,user_id = :userId,card_type = :cardType,card_bin = :cardBin,area_code = :areaCode,check_code = :checkCode,state = :state,init_date = :initDate,init_oper = :initOper,open_oper = :openOper,open_channel = :openChannel,open_date = :openDate,valid_date = :validDate,merchant_code = :merchantCode where card_no = :cardNo", identityAccountList);
  }
  
  public IdentityAccountVO queryIdentifyCardByNo(String cardNo)
  {
    try
    {
      StringBuilder sql = new StringBuilder("select  a.card_no,a.password,a.user_id,a.card_type,a.card_bin,a.area_code,a.check_code,a.state,a.init_date,a.init_oper,a.open_oper,a.open_channel,a.open_date,a.valid_date,a.merchant_code  from ms_identity_account as a ");
      sql.append(" ,ms_user_base ub ,ms_merchant mm  where  a.user_id = ub.user_id and a.merchant_code = mm.merchant_code and mm.is_valid=1  and ub.is_valid=1 and  a.card_no=:cardNo");
      
      IdentityAccountVO param = new IdentityAccountVO();
      param.setCardNo(cardNo);
      return (IdentityAccountVO)queryForObject(sql.toString(), param, IdentityAccountVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public String queryUserIdByCardNo(String cardNo)
  {
    try
    {
      IdentityAccountVO param = new IdentityAccountVO();
      param.setCardNo(cardNo);
      
      return (String)queryForObject("select ur.user_id from ms_identity_account ia,ms_user_relationship ur where ia.user_id = ur.relate_user_id and ur.is_valid = 1 and  ia.card_no = :cardNo", param, String.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
}
